package com.xuxueli.commontdemo.util;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.parser.ParserException;
import com.alibaba.druid.sql.parser.SQLParserUtils;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.jayway.jsonpath.JsonPath;
import com.xuxueli.commontdemo.entity.APIDATA;
import com.xuxueli.commontdemo.exception.ErrorException;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.HttpResponse;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.time.temporal.ChronoField;
import java.time.temporal.TemporalAccessor;
import java.util.Date;
import java.util.*;

/**
 * 测试数据库统计数据,特殊字符测试
 */
@Slf4j
@Component
public class TestDb {

    @Value("${banner.images.path:}")
    private String logoSavePath;

    private static final String TABLE_NAME = "#TABLE_NAME#";
    private static final String COLUMNS = "#COLUMNS#";
    private static final String COLUMN_VALUES = "#COLUMN_VALUES#";
    private static final String INSERT_SQL_TMP = "insert into #TABLE_NAME#(#COLUMNS#)values(#COLUMN_VALUES#)";


    public static void sqlFormat(String sql) {
        System.out.println("格式化之前：");
        System.out.println(sql);
        System.out.println("格式化之后：");
        try {
            SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, "postgresql");
            List<SQLStatement> statementList = parser.parseStatementList();
            sql = SQLUtils.toSQLString(statementList, "postgresql");
            System.out.println(sql);
        } catch (ParserException e) {
            System.out.println("SQL转换中发生了错误："+e.getMessage());
        }
    }

    private static String getJsonValue() {
        String valueString = StringUtils.EMPTY;
        String bookJson = "{\n" +
                "  \"store\": {\n" +
                "    \"book\":[\n" +
                "      { \"category\": \"reference\",\n" +
                "        \"author\": \"Nigel Rees\",\n" +
                "        \"title\": \"Sayings of the Century\",\n" +
                "        \"price\": 8.95\n" +
                "      },\n" +
                "      { \"category\": \"fiction\",\n" +
                "        \"author\": \"J. R. R. Tolkien\",\n" +
                "        \"title\": \"The Lord of the Rings\",\n" +
                "        \"isbn\": \"0-395-19395-8\",\n" +
                "        \"price\": 22.99\n" +
                "      }\n" +
                "    ],\n" +
                "    \"bicycle\": {\n" +
                "      \"color\": \"red\",\n" +
                "      \"price\": 19.95\n" +
                "    }\n" +
                "  }\n" +
                "}\n";
        StringBuilder stringBuilder = new StringBuilder();
        try {
            //输出book节点中某一条数据
            Object reads = JsonPath.read(bookJson, "$.store.book[1].author");
            //输出json节点某一个对象
            Object readss = JsonPath.read(bookJson, "$.store.bicycle.color");
            //输出book节点中所有对象
            Object readsss = JsonPath.read(bookJson, "$.store.book[*]");
            //输出book节点中所有价格小于10的对象
            Object readssss = JsonPath.read(bookJson, "$.store.book[?(@.price<10)]");
            //输出book节点中所有含有isbn的对象
            Object readsssss = JsonPath.read(bookJson, "$.store.book[?(@.isbn)]");
            if(null!=reads){
                stringBuilder.append("输出book节点中某一条数据："+reads.toString()).append("\n");
            }
            Optional<Object> readss1 = Optional.ofNullable(readss);
            if(readss1.isPresent()){
                stringBuilder.append("输出json节点某一个对象："+readss1.get().toString()).append("\n");
            }
            Optional<Object> readsss1 = Optional.ofNullable(readsss);
            if(readsss1.isPresent()){
                if(readsss1.get() instanceof List){
                    System.out.println(JSONObject.toJSONString(readsss1.get(),SerializerFeature.WriteMapNullValue,SerializerFeature.WriteDateUseDateFormat));
                }
                stringBuilder.append("输出book节点中所有对象："+readsss1.get().toString()).append("\n");
            }
            Optional<Object> readsss1s = Optional.ofNullable(readssss);
            if(readsss1s.isPresent()){
                stringBuilder.append("输出book节点中所有价格小于10的对象："+readsss1s.get().toString()).append("\n");
            }
            Optional<Object> readsss1ss = Optional.ofNullable(readsssss);
            if(readsss1ss.isPresent()){
                stringBuilder.append("输出book节点中所有含有isbn的对象："+readsss1ss.get().toString()).append("\n");
            }
            if(null!=stringBuilder){
                valueString = stringBuilder.toString();
            }
        }catch (Exception ex){
            System.out.println("解析数据失败");
        }
        return valueString;
    }

    public static void main(String[] args) {
        String sqlstr = "create table `tb_user` (\n" +
                "  `id` varchar(22) not null COMMENT '主键id',\n" +
                "  `usercode` varchar(11) DEFAULT null COMMENT '手机号',\n" +
                "  `name` varchar(10))";
        //sqlFormat(sqlstr);//格式化sql
        getJsonValue();//获取jsonpath值
//        HikariConfig hikariConfig = getHikariConfig("oracle");
//        HikariDataSource pool = new HikariDataSource(hikariConfig);
//        String oracleSql = "select\n" +
//                "count(*)\n" +
//                "from QA.API_DATA\n" +
//                "WHERE CREATED_TIME >= to_date('2022-07-19 13:14:10','yyyy-MM-dd hh24:mi:ss') and CREATED_TIME <= to_date('2022-07-26 13:14:16','yyyy-MM-dd hh24:mi:ss')";
//        String mysqlSql = "SELECT id, english_name, china_name, department_id, department_name, visible, apply_key, create_time, create_by, update_time, update_by, deleted FROM apply_managenment";
//        String pgsqlSql = "SELECT aggfnoid, aggkind, aggnumdirectargs, aggtransfn, aggfinalfn, aggcombinefn, aggserialfn, aggdeserialfn, aggmtransfn, aggminvtransfn, aggmfinalfn, aggfinalextra, aggmfinalextra, aggsortop, aggtranstype, aggtransspace, aggmtranstype, aggmtransspace, agginitval, aggminitval FROM pg_aggregate";
//        Map<String,Object> result = new HashMap<>();
//        ResultSet resultSet = null;
//        try {
//            if(oracleSql.startsWith("select") || oracleSql.startsWith("SELECT")){
//                resultSet = pool.getConnection().prepareStatement(oracleSql).executeQuery();
//            }else{
//                pool.getConnection().prepareStatement(oracleSql).execute();
//            }
//            poolState(pool,true,"sdfsa");
//            Integer active = new HikariDataSourcePoolMetadata(pool).getActive();
//            System.out.println("当前激活数量："+active);
//            List<Map<String,Object>> openApiResponseDataList = new ArrayList<>();
//            // 查询出来的字段信息
//            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//            while (resultSet.next()) {
//                Map<String,Object> responseItem = new HashMap<>();
//                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
//                    int columnType = resultSetMetaData.getColumnType(i);
//                    if (JdbcTypeUtils.isLob(columnType)){
//                        continue;
//                    }
//                    String columnName = resultSetMetaData.getColumnName(i).trim();
//                    if (null == resultSet.getObject(i)) {
//                        responseItem.put(columnName, "");
//                    } else {
//                        if (JdbcTypeUtils.isDate(columnType)) {
//                            responseItem.put(columnName, resultSet.getObject(i).toString());
//                        } else {
//                            responseItem.put(columnName, resultSet.getObject(i));
//                        }
//                    }
//                }
//                openApiResponseDataList.add(responseItem);
//            }
//            List<APIDATA> list = new ArrayList<>();
//            Long row = 6L;
//            for (int i = 0; i < 10000; i++) {
//                APIDATA apidata = new APIDATA();
//                apidata.setID(row);
//                apidata.setFILE_NAME("测试数据");
//                apidata.setCREATED_TIME(new Date());
//                apidata.setTEMP_NAME("测试数据");
//                apidata.setDS_ID(row);
//                list.add(apidata);
//                row++;
//            }
//            //affectRowMore(pool.getConnection(),"insert into QA.API_DATA(ID,FILE_NAME,TEMP_NAME,DS_ID) values(?,?,?,?)",list);
//            result.put("data",openApiResponseDataList);
//            if(StringUtils.containsIgnoreCase(oracleSql,";")){
//                oracleSql = oracleSql.replace(";","");
//            }
//            result.put("total",getTotal(pool.getConnection(),oracleSql));
//            if(StringUtils.containsIgnoreCase(oracleSql,"count(*)") || StringUtils.containsIgnoreCase(oracleSql,"count(1)")){
//                result.put("sum",getSum(pool.getConnection(),oracleSql));
//            }else{
//                result.put("sum",getTotal(pool.getConnection(),oracleSql));
//            }
//
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            try {
//                if (pool.getConnection() != null) {
//                    pool.getConnection().close();
//                }
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        System.out.println(result);
//        String mysqlSqlSpecial = "SELECT help_keyword_id,name,note FROM mysql.help_keyword where help_keyword_id in(888,889,890)";
        //测试特殊字符
//        getListBySet("com.mysql.cj.jdbc.Driver","jdbc:mysql://localhost:3306?serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8","root","123456","list",mysqlSqlSpecial);
        HikariConfig hikariConfig = getHikariConfig("mysql");
        TestDb testDb = new TestDb();
//        testDb.persistData(hikariConfig);//持久化,ok
//        testDb.execProcedureNoPara(hikariConfig,"proc_stu");//执行存储过程,ok
//        testDb.execMoreSqlNo(hikariConfig,"mysql","test");//执行多个sql语句,ok
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.DAY_OF_MONTH, -15);//前15天
        Date time = calendar.getTime();
        System.out.println(DateUtils.formatDate(time,"yyyy-MM-dd"));
//        try {
        //执行多个sql语句，包括新增，更新，删除，加事务处理
//            testDb.insertInTransaction(hikariConfig);,ok
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        try {
            testDb.moreSqlByOne(hikariConfig);//拼接多个sql通过一个sql执行,ok
            //testDb.insertInTransaction(hikariConfig);//拼接多个sql通过一个sql执行,ok
        } catch (SQLException e) {
            e.printStackTrace();
        }
        execProcedureNoPara(hikariConfig,null);
    }

    //@Transactional(rollbackFor=Exception.class)
    public ResponseResult persistData(HikariConfig hikariConfig) {
        log.info("执行入库");
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> param = new HashMap<>();
        param.put("engine_name","测试");
        param.put("device_type",1);
        param.put("cost_name","测试");
        param.put("cost_value",1.8D);
        param.put("last_update",new Date());
        param.put("comment","学而思");
        Map<String, Object> parasm = new HashMap<>();
        parasm.put("engine_name","测试");
        parasm.put("device_type",1);
        parasm.put("cost_name","测试");
        parasm.put("cost_value",1.8D);
        parasm.put("last_update",new Date());
        parasm.put("comment","学而思");
        list.add(param);
        list.add(parasm);
        Savepoint savepoint1 = null;
        for (Map<String, Object> params:list){
            if (params == null || params.isEmpty()) {
                System.out.println(ResponseResult.error("入库字段空"));
            }
            Connection conn = null;
            PreparedStatement statement = null;
            String insertSql = null;
            try {
                HikariDataSource pool = new HikariDataSource(hikariConfig);
                conn =pool.getConnection();
                conn.setAutoCommit(false);
                StringBuilder columnBuilder = new StringBuilder();
                StringBuilder columnValueBuilder = new StringBuilder();
                params.keySet().forEach(column -> {
                    columnBuilder.append(column).append(",");
                    columnValueBuilder.append("?,");
                });
//                statement = conn.prepareStatement("delete from engine_cost");
//                statement.execute();
                savepoint1 = conn.setSavepoint("Savepoint1");
                String columns = columnBuilder.toString().substring(0, columnBuilder.toString().length() - 1);
                String columnValues = columnValueBuilder.toString()
                        .substring(0, columnValueBuilder.toString().length() - 1);
                insertSql = INSERT_SQL_TMP.replace(TABLE_NAME, "engine_cost")
                        .replace(COLUMNS, columns).replace(COLUMN_VALUES, columnValues);
                statement = conn.prepareStatement(insertSql);
                int index = 1;
                Map<String, String> dateColumns = new HashMap<>();
                dateColumns.put("last_update","yyyy-mm-dd hh:mm:ss");
                for (Map.Entry<String, Object> entry : params.entrySet()) {
                    if (entry.getValue() == null) {
                        statement.setObject(index++, null);
                        continue;
                    }
                    if (entry.getValue() instanceof Map || entry.getValue() instanceof List) {
                        statement.setObject(index++, JSONObject.toJSONString(entry.getValue()));
                    } else if (dateColumns != null && dateColumns.containsKey(entry.getKey())) {
                        //statement.setTimestamp(index++, parseDate(entry.getValue(), dateColumns.get(entry.getKey())));
//                    LocalDateTime time = LocalDateTime.of(LocalDate.from(LocalDateTime.now()), LocalTime.MAX);
//                    DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//                    String formatTime = format.format(time);
//                    LocalDateTime endTime = LocalDateTime.parse(formatTime, format);
                        Date date = new Date();
                        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String dateStr = sdf.format(date);
                        Timestamp endTime = Timestamp.valueOf(dateStr); //2017-05-06 15:54:21.0
                        statement.setTimestamp(index++, endTime);
                    } else {
                        statement.setObject(index++, entry.getValue());
                    }
                }
                boolean result = statement.execute();
                conn.prepareCall(insertSql);
                conn.commit();
                System.out.println(result);
            } catch (Exception e) {
                log.error("数据持久化失败,{},{}", insertSql, params, e);
                System.out.println(ResponseResult.error(e.getMessage()));
                try{
                    if(conn!=null){
                        conn.rollback(savepoint1);
                    }
                }catch(SQLException se2){
                    se2.printStackTrace();
                }
                //TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//回滚
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                    if (statement != null) {
                        statement.close();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return ResponseResult.ok();
    }

    private static Timestamp parseDate(Object value, String format) {
        if (value == null || StringUtils.isBlank(format)) {
            return null;
        }
        try {
            DateTimeFormatter dateTimeFormatter = new DateTimeFormatterBuilder().appendPattern(format.trim())
                    .parseDefaulting(ChronoField.DAY_OF_MONTH, 1)
                    .parseDefaulting(ChronoField.HOUR_OF_DAY, 0)
                    .parseDefaulting(ChronoField.MINUTE_OF_HOUR, 0)
                    .parseDefaulting(ChronoField.SECOND_OF_MINUTE, 0)
                    .parseDefaulting(ChronoField.MILLI_OF_SECOND, 0)
                    .toFormatter(Locale.ENGLISH).withZone(ZoneId.systemDefault());
            TemporalAccessor temporalAccessor = dateTimeFormatter.parse(value.toString());
            Instant instant = Instant.from(temporalAccessor);
            long mills = instant.toEpochMilli();
            return new Timestamp(mills);
        } catch (Exception e) {
            log.error("日期解析失败:{},{}", value, format, e);
        }
        return null;
    }

    /**
     * 执行多个sql语句
     * execute与executeUpdate
     * 同点：都可以执行增加，删除，修改
     * 不同1：execute可以执行查询语句
     * 然后通过getResultSet，把结果集取出来
     *
     * 不同2：execute返回boolean类型，true表示执行的是查询语句，false表示执行的是insert,delete,update等等
     * executeUpdate返回的是int，表示有多少条数据受到了影响
     */
    public static void execMoreSqlNo(HikariConfig hikariConfig,String scheName,String tableName) {
        HikariDataSource pool = new HikariDataSource(hikariConfig);
        Connection conn = null; // 数据库连接对象
        PreparedStatement psm = null;
        ResultSet res = null; // 结果集对象
        String sql = StringUtils.EMPTY;
        String newTableName = tableName+"_copy";
        try {
            // 获取数据库连接
            conn = pool.getConnection();
            conn.setAutoCommit(false);
            //是否存在表
            sql = "select count(1) as countNum from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='"+scheName+"' and TABLE_NAME='"+newTableName+"'";
            psm = conn.prepareStatement(sql);
            res = psm.executeQuery();
            while (res.next()) {
                // 获取列值
                int countNum = res.getInt("countNum");
                if(countNum<=0){
                    sql = "CREATE TABLE IF NOT EXISTS "+newTableName+" SELECT * FROM "+tableName+" WHERE 1=2";
                    psm = conn.prepareStatement(sql);
                    psm.execute();
                    sql = "select count(1) as countColumn from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = \"scheName\" and TABLE_NAME = \"newTableName\" and column_name = 'create_time'";
                    psm = conn.prepareStatement(sql);
                    res = psm.executeQuery();
                    while (res.next()) {
                        int countColumn = res.getInt("countColumn");
                        if(countColumn<=0){
                            sql = "alter table "+newTableName+" add create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'";
                            psm = conn.prepareStatement(sql);
                            psm.execute();
                        }else{
                            sql = "ALTER TABLE "+newTableName+" MODIFY COLUMN create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间'";
                            psm = conn.prepareStatement(sql);
                            psm.execute();
                        }
                        sql = "insert into "+newTableName+" SELECT *,NOW() FROM "+tableName;
                        psm = conn.prepareStatement(sql);
                        psm.execute();
                    }
                }else{
                    sql = "select count(1) as countColumn from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = '"+scheName+"' and TABLE_NAME = '"+newTableName+"' and column_name = 'create_time'";
                    psm = conn.prepareStatement(sql);
                    res = psm.executeQuery();
                    while (res.next()) {
                        int countColumn = res.getInt("countColumn");
                        if(countColumn<=0){
                            sql = "alter table "+newTableName+" add create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'";
                            psm = conn.prepareStatement(sql);
                            psm.execute();
                        }else{
                            sql = "ALTER TABLE "+newTableName+" MODIFY COLUMN create_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间'";
                            psm = conn.prepareStatement(sql);
                            psm.execute();
                        }
                        sql = "insert into "+newTableName+" SELECT *,NOW() FROM "+tableName;
                        psm = conn.prepareStatement(sql);
                        psm.execute();
                    }
                }
            }
            sql = "delete FROM "+tableName;
            psm = conn.prepareStatement(sql);
            psm.execute();
            conn.commit();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            if(null!=conn){
                try {
                    conn.rollback();
                    psm.executeUpdate("drop table "+newTableName);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        } finally {
            // 关闭数据库操作对象
            closeOperate(res, psm, conn);
        }
    }

    /**
     * 拼接多个sql通过一个sql执行，授权
     * @param hikariConfig
     * @throws SQLException
     */
//    1.grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。
//    grant select on testdb.* to common_user@’%’
//    grant insert on testdb.* to common_user@’%’
//    grant update on testdb.* to common_user@’%’
//    grant delete on testdb.* to common_user@’%’
//    或者，用一条 MySQL 命令来替代：
//    2.grant select, insert, update, delete on testdb.* to common_user@’%’
//    grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。
//    grant 创建、修改、删除 MySQL 数据表结构权限。
//    grant create on testdb.* to developer@’192.168.0.%’;
//    grant alter on testdb.* to developer@’192.168.0.%’;
//    grant drop on testdb.* to developer@’192.168.0.%’;
//    grant 操作 MySQL 外键权限。
//    grant references on testdb.* to developer@’192.168.0.%’;
//    grant 操作 MySQL 临时表权限。
//    grant create temporary tables on testdb.* to developer@’192.168.0.%’;
//    grant 操作 MySQL 索引权限。
//    grant index on testdb.* to developer@’192.168.0.%’;
//    grant 操作 MySQL 视图、查看视图源代码 权限。
//    grant create view on testdb.* to developer@’192.168.0.%’;
//    grant show view on testdb.* to developer@’192.168.0.%’;
//    grant 操作 MySQL 存储过程、函数 权限。
//    grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status
//    grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure
//    grant execute on testdb.* to developer@’192.168.0.%’;
//    3.grant 普通 DBA 管理某个 MySQL 数据库的权限。
//    grant all privileges on testdb to dba@’localhost’
//    其中，关键字 “privileges” 可以省略。
//    4.grant 高级 DBA 管理 MySQL 中所有数据库的权限。
//    grant all on *.* to dba@’localhost’
//    5.MySQL grant 权限，分别可以作用在多个层次上。
//    grant 作用在整个 MySQL 服务器上：
//    grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
//    grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
//    grant 作用在单个数据库上：
//    grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
//    grant 作用在单个数据表上：
//    grant select, insert, update, delete on testdb.orders to dba@localhost;
//    grant 作用在表中的列上：
//    grant select(id, se, rank) on testdb.apache_log to dba@localhost;
//    grant 作用在存储过程、函数上：
//    grant execute on procedure testdb.pr_add to ’dba’@’localhost’
//    grant execute on function testdb.fn_add to ’dba’@’localhost’
//    6.查看 MySQL 用户权限
//    查看当前用户(自己)权限：
//    show grants;
//    查看其他 MySQL 用户权限：
//    show grants for dba@localhost;
//    7.撤销已经赋予给 MySQL 用户权限的权限。
//    revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：
//    grant all on *.* to dba@localhost;
//    revoke all on *.* from dba@localhost;
//    8.MySQL grant、revoke 用户权限注意事项
//    grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。
//    如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“
//    grant select on testdb.* to dba@localhost with grant option;

    public void moreSqlByOne(HikariConfig hikariConfig) throws SQLException{
        HikariDataSource pool = new HikariDataSource(hikariConfig);
        Connection conn = null; // 数据库连接对象
        Statement stmt = null;
        List<String> sqlLists = Arrays.asList("drop table if exists test_role;",
                "CREATE TABLE test_role (\n" +
                        "  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
                        "  code varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '编码',\n" +
                        "  name varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',\n" +
                        "  description varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',\n" +
                        "  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                        "  create_by varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT 'test' COMMENT '创建者',\n" +
                        "  PRIMARY KEY (id) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;",
                "alter table test_role add update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '新时间';\n",
                "CREATE index idx_code on test_role(code);\n",
                "insert into test_role(code,name) SELECT code,message FROM test_copy;");
        try {
            //alter table test_role rename test_roles;
            //drop index idx_code;
            //alter table test_role add index idx_code (code);
            //是否存在表
            //select count(1) into countTable from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='oceanus' and TABLE_NAME='testjsonJaray_res_data_copy';
            //复制表结构
            //CREATE TABLE IF NOT EXISTS testjsonJaray_res_data_copy SELECT * FROM test WHERE 1=2;
            //添加创建时间
            //select count(1) into countColumns from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='oceanus' and TABLE_NAME = 'testjsonJaray_res_data_copy' and column_name = 'create_time';
            //alter table testjsonJaray_res_data_copy add create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
            //显示  mysql -uroot -pthink -P3306   use yest
            //show databases;show tables;
            //判断是否存在索引
            //SELECT * FROM information_schema.statistics WHERE table_schema='mysql' and table_name = 'test_role' and index_name = 'idx_code';
            //删除存在的主键
            //alter table test drop primary key;
            //创建主键
            //alter table test add primary key(id);
            //判断是否存在约束
            //select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME ='test_role';
            //是否存在试图
            //select * from information_schema.VIEWS where TABLE_NAME ='test_role';
            //授权
            //grant all privileges on dbname.* to username@localhost identified by 'passwd';
            //创建用户
            //create user username@localhost identified by 'passwd';
            //查看用户信息
            //select user,host from mysql.user;
            //mysql刷新权限命令：FLUSH PRIVILEGES;（一般用于数据库用户信息更新后）
            //创建试图
//            create or replace view role_vie as select * from test_role;
//            select * from role_vie;
//            create user user01;
//            identified by 'passwd';
//            grant select on role_vie to user01;
//            grant connect to user01;
//            grant create synonym to user01;
            //创建同义词
//            create synonym twerwe for mysql.role_vie;
            //SELECT * FROM twerwe;
            //mysql with语法
//            with t as (
//                    select * from consumer
//            )
//            select * from t
            //orace merge into语法
//            merge into table_name u
//            using
//                    (
//                            <foreach collection="list" index="index" item="item" open=""
//                    close="" separator="union">
//                            SELECT #{item.stlty} as stlty,#{item.stlnr} as stlnr,#{item.stlkn} as stlkn,#{item.stpoz} as stpoz,#{item.datuv} as datuv,#{item.andat} as andat,#{item.aedat} as aedat,#{item.idnrk} as idnrk,#{item.pswrk} as pswrk,#{item.postp} as postp,#{item.posnr} as posnr,#{item.maktx} as maktx,#{item.vornr} as vornr,#{item.stvkn} as stvkn,#{item.plnnr} as plnnr,#{item.plnkn} as plnkn
//                    FROM dual
//                </foreach>
//                ) t
//            on (u.stlty = t.stlty and u.stlnr = t.stlnr and u.stlkn = t.stlkn and u.stpoz = t.stpoz)
//            when matched then
//            update set u.datuv = t.datuv,u.andat = t.andat,u.aedat = t.aedat,u.idnrk = t.idnrk,u.pswrk = t.pswrk,u.postp = t.postp,u.posnr = t.posnr,u.maktx = t.maktx,u.vornr = t.vornr,u.stvkn = t.stvkn,u.plnnr = t.plnnr,u.plnkn = t.plnkn,u.updatedtime = sysdate
//            when not matched then
//            insert (stlty,stlnr,stlkn,stpoz,datuv,andat,aedat,idnrk,pswrk,postp,posnr,maktx,vornr,stvkn,plnnr,plnkn,createdtime) VALUES(t.stlty,t.stlnr,t.stlkn,t.stpoz,t.datuv,t.andat,t.aedat,t.idnrk,t.pswrk,t.postp,t.posnr,t.maktx,t.vornr,t.stvkn,t.plnnr,t.plnkn,sysdate)
            //where只能出现一次，如果这里使用了where，delete后面的where就无效了
//            merge into test1 t1
//            using (select id from test2 t2 where t2.name = 'aa') t2
//            on (t1.id = t2.id)
//            when matched then
//            update  set t1.name = t1.name
//            delete where t1.id =t2.id;
//            commit;
            //mysql语法
//            UPDATE a
//            INNER JOIN b
//            ON a.userName = b.userName
//            SET a.password = b.password
            //pgsql,sqlserver
            //update table a set name=b.name from table B b  where a.id=b.id;
            conn = pool.getConnection();
            //关闭自动提交，即开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            for (String sqlString:sqlLists){
                System.out.println(sqlString);
                stmt.addBatch(sqlString);
            }
            stmt.executeBatch();
            stmt.clearBatch();
            //stmt.execute(sql);
            //提交事务
            conn.commit();
        } catch (SQLException e) {
            if(null!=conn){
                conn.rollback();
                System.out.println("Exception:");
                System.out.println(e.getMessage());
                stmt.executeUpdate("drop table IF EXISTS test_role");
            }
        } finally {
            // 关闭数据库操作对象
            closeOperate(null, stmt, conn);
        }
    }

    /**
     * 执行多个sql语句，包括新增，更新，删除
     * @param hikariConfig
     * @throws SQLException
     */
    public void insertInTransaction(HikariConfig hikariConfig) throws SQLException{
        HikariDataSource pool = new HikariDataSource(hikariConfig);
        Connection conn = null; // 数据库连接对象
        Statement stmt = null;
        String[] sqlList = new String[] {
                "CREATE TABLE jdbc_test (\n" +
                        "  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
                        "  code varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '编码',\n" +
                        "  name varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',\n" +
                        "  description varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',\n" +
                        "  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                        "  create_by varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT 'test',\n" +
                        "  PRIMARY KEY (id) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC",
                "delete from jdbc_test",
                "alter table jdbc_test AUTO_INCREMENT = 1",
                "insert into jdbc_test(id,code,name) values (null, 'aaa','111')",
                "insert into jdbc_test(id,code,name) values (null, 'bbb', '222')",
                "insert into jdbc_test(id,code,name) values (null, 'ccc', '333')",
                //下面这条sql会失败，因为已经存在主键为2的记录
                "insert into jdbc_test(id,code,name) values (2, 'ddd','444')"};
        List<String> sqlLists = Arrays.asList("CREATE TABLE jdbc_test (\n" +
                        "  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',\n" +
                        "  code varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '编码',\n" +
                        "  name varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',\n" +
                        "  description varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '描述',\n" +
                        "  create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n" +
                        "  create_by varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT 'test',\n" +
                        "  PRIMARY KEY (id) USING BTREE\n" +
                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC",
                "delete from jdbc_test");
        try {
            conn = pool.getConnection();
            //关闭自动提交，即开启事务
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
//            for (String sql : sqlList) {
//                stmt.executeUpdate(sql);
//            }
//            //提交事务
//            conn.commit();
            //或者
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            stmt.clearBatch();
            conn.commit();
        } catch (SQLException e) {
            if(null!=conn){
                conn.rollback();
                System.out.println("Exception:");
                System.out.println(e.getMessage());
                stmt.executeUpdate("drop table IF EXISTS  jdbc_test");
            }
        } finally {
            // 关闭数据库操作对象
            closeOperate(null, stmt, conn);
        }
    }


    /**
     * 调用存储过程,无参数
     * @author pan_junbiao
     */
    public static void execProcedureNoPara(HikariConfig hikariConfig,String callName) {
        HikariDataSource pool = new HikariDataSource(hikariConfig);
        Connection conn = null; // 数据库连接对象
        CallableStatement clbStmt = null; // CallableStatement对象
        ResultSet res = null; // 结果集对象
        try {
            // 获取数据库连接
            conn = pool.getConnection();
            // 创建CallableStatement对象
            clbStmt = conn.prepareCall("{CALL proc_test()}");
//            // 设置输入参数
//            clbStmt.setInt(1, 3); // 查询第3页数据
//            clbStmt.setInt(2, 10); // 每页10条数据
//
//            // 注册输出参数
//            clbStmt.registerOutParameter(3, Types.INTEGER);
//            clbStmt.registerOutParameter(4, Types.INTEGER);

            // 执行调用存储过程，并获取结果集
            res = clbStmt.executeQuery();
            // 循环遍历结果集
            while (res.next()) {
                // 获取列值
                int id = res.getInt("id");
                String code = res.getString("title");
                //Timestamp createTime = res.getTimestamp("create_time");
                // 输出列值
                System.out.println("编号：" + id + "  编号：" + code + "  创建时间：" + null);
            }
            // 获取输出参数值
//            int totalCount = clbStmt.getInt(3);
//            int totalPage = clbStmt.getInt(4);
//            System.out.println("数据总数：" + totalCount + " 总页数：" + totalPage);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            // 关闭数据库操作对象
            closeOperate(res, clbStmt, conn);
        }
    }

    /**
     * 调用存储过程,带参数
     * @author pan_junbiao
     */
    public static void execProcedure(HikariConfig hikariConfig) {
        HikariDataSource pool = new HikariDataSource(hikariConfig);
        Connection conn = null; // 数据库连接对象
        CallableStatement clbStmt = null; // CallableStatement对象
        ResultSet res = null; // 结果集对象
        try {
            // 获取数据库连接
            conn = pool.getConnection();
            // 创建CallableStatement对象
            clbStmt = conn.prepareCall("{CALL proc_search_user(?,?,?,?)}");

            // 设置输入参数
            clbStmt.setInt(1, 3); // 查询第3页数据
            clbStmt.setInt(2, 10); // 每页10条数据

            // 注册输出参数
            clbStmt.registerOutParameter(3, Types.INTEGER);
            clbStmt.registerOutParameter(4, Types.INTEGER);

            // 执行调用存储过程，并获取结果集
            res = clbStmt.executeQuery();
            // 循环遍历结果集
            while (res.next()) {
                // 获取列值
                int id = res.getInt("id");
                String name = res.getString("name");
                Timestamp createTime = res.getTimestamp("create_time");

                // 输出列值
                System.out.println("编号：" + id + "  姓名：" + name + "  创建时间：" + createTime);
            }
            // 获取输出参数值
            int totalCount = clbStmt.getInt(3);
            int totalPage = clbStmt.getInt(4);
            System.out.println("数据总数：" + totalCount + " 总页数：" + totalPage);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } finally {
            // 关闭数据库操作对象
            closeOperate(res, clbStmt, conn);
        }
    }

    /**
     * 关闭数据库操作对象
     *
     * @author pan_junbiao
     * @param res  ResultSet对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeOperate(ResultSet res, Statement stmt, Connection conn) {
        try {
            // 关闭ResultSet对象
            if (res != null) {
                res.close();
            }
            // 关闭Statement对象
            if (stmt != null) {
                stmt.close();
            }
            // 关闭Connection对象
            if (conn != null) {
                conn.close();
            }
            System.out.println("关闭数据库操作对象完成");
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /**
     * 获取激活及总连接数
     * @param dataSource
     * @param ifLog
     * @param prefix
     * @return
     */
    public static String poolState(HikariDataSource dataSource, boolean ifLog, String... prefix) {
        String poolName = dataSource.getPoolName();
        HikariPoolMXBean mx = dataSource.getHikariPoolMXBean();
        String format = String.format("%s - %sstats (total=%d, active=%d, idle=%d, waiting=%d)",
                poolName, (prefix.length > 0 ? prefix[0] : ""),
                mx.getTotalConnections(), mx.getActiveConnections(), mx.getIdleConnections(), mx.getThreadsAwaitingConnection());
        if (ifLog) {
            System.out.println(format);
        }
        return format;
    }

    /**
     * 获取sql驱动连接
     * @param type
     * @return
     */
    public static HikariConfig getHikariConfig(String type){
        Properties properties = new Properties();
        HikariConfig hikariConfig = new HikariConfig();
        if("mysql".equals(type)){
            hikariConfig.setJdbcUrl("jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8");//mysql
            hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
            hikariConfig.setUsername("root");
            hikariConfig.setPassword("123456");
            hikariConfig.setConnectionTestQuery("SELECT 1");
            hikariConfig.setMaximumPoolSize(15);
            hikariConfig.setMaxLifetime(30000);
            hikariConfig.setMinimumIdle(2);
            hikariConfig.addDataSourceProperty("remarksReporting", "true");
            hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
            hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
            hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            //properties.setProperty("remarksReporting", "true");
            hikariConfig.setDataSourceProperties(properties);
        }else if("oracle".equals(type)){
            StringBuffer stringBuffer = new StringBuffer();
            String host = "10.0.49.28:1521";
            String[] hosts = host.split(",");
            for (String h : hosts){
                String[] ips = h.split(":");
                stringBuffer.append("(ADDRESS = (PROTOCOL = TCP)(HOST = " + ips[0] +")(PORT = " + ips[1] +"))");
            }
            String url = "jdbc:oracle:thin:@" +
                    "(DESCRIPTION =(ADDRESS_LIST ="+ stringBuffer.toString() + "(LOAD_BALANCE = yes)(FAILOVER=ON))" +
                    "(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = "+ "helowin" +")(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))";
            //hikariConfig.setJdbcUrl("jdbc:oracle:thin:@/helowin");//oracle
            hikariConfig.setJdbcUrl(url);//oracle
            hikariConfig.setDriverClassName("oracle.jdbc.driver.OracleDriver");
            hikariConfig.setUsername("qa");
            hikariConfig.setPassword("qa321");
            hikariConfig.setConnectionTestQuery("SELECT 1 from dual");
            hikariConfig.setMaximumPoolSize(15);
            hikariConfig.setMaxLifetime(30000);
            hikariConfig.setMinimumIdle(2);
            hikariConfig.addDataSourceProperty("remarksReporting", "true");
            hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
            hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
            hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            //properties.setProperty("remarksReporting", "true");
            hikariConfig.setDataSourceProperties(properties);
        }else if("postgresql".equals(type)){
            hikariConfig.setJdbcUrl("jdbc:postgresql://10.0.49.145:5432/kong");
            hikariConfig.setDriverClassName("org.postgresql.Driver");
            hikariConfig.setUsername("kong");
            hikariConfig.setPassword("123456");
            hikariConfig.setMaximumPoolSize(100);
            hikariConfig.setConnectionTestQuery("SELECT 1");
            hikariConfig.setPoolName("Hikari");
            hikariConfig.setAutoCommit(false);
            hikariConfig.setConnectionTimeout(60000);
            hikariConfig.setIdleTimeout(400000);
            hikariConfig.setMaxLifetime(480000);
            hikariConfig.setMinimumIdle(10);
            hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
            hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
            hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            hikariConfig.addDataSourceProperty("useServerPrepStmts", "true");
        }else if("sqlserver".equals(type)){
            hikariConfig.setJdbcUrl("jdbc:sqlserver://127.0.0.1:1433;databaseName=test");
            hikariConfig.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
            hikariConfig.setUsername("sqlserver");
            hikariConfig.setPassword("sqlserver");
            hikariConfig.setMaximumPoolSize(100);
            hikariConfig.setConnectionTestQuery("SELECT 1");
            hikariConfig.setPoolName("Hikari");
            hikariConfig.setAutoCommit(false);
            hikariConfig.setConnectionTimeout(60000);
            hikariConfig.setIdleTimeout(400000);
            hikariConfig.setMaxLifetime(480000);
            hikariConfig.setMinimumIdle(10);
            hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
            hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
            hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
            hikariConfig.addDataSourceProperty("useServerPrepStmts", "true");
        }



        return hikariConfig;
    }

    /**
     * 测试特殊字符
     * @param driver
     * @param url
     * @param userName
     * @param password
     * @param type
     * @param sql
     * char(9) 表示水平制表符 （tab键 \t）
     * char(10) 表示换行键 （\n）
     * char(13) 表示回车键 （\r）
     *
     */
    private static void getListBySet(String driver,String url,String userName,String password,String type,String sql){
        //INSERT INTO help_keyword (help_keyword_id,name,note) VALUES(888,char(9),'tab键'),(889,char(10),'换行键'),(890,char(13),'回车键');
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,userName,password);
            statement = connection.prepareStatement(sql);
            if("list".equals(type)){
                resultSet = statement.executeQuery(sql);
                String valie = "ssa\rsdfas";
                while (resultSet.next()){
                    String resultSetString = resultSet.getString("name");
                    if(valie.contains(resultSetString)){
                        System.out.println("含有特殊字符："+resultSet.getString("note"));
                    }
                }
            }else{
                statement.execute(sql);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private byte[] getBody(HttpResponse response) {
        try{
            InputStream in = response.getEntity().getContent();
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            byte[] bytes = new byte[1024];
            int len;
            while ((len = in.read(bytes)) != -1) {
                out.write(bytes, 0, len);
            }
            return out.toByteArray();
        } catch (IOException e) {
            log.error("外源调用异常", e);
            throw new ErrorException(500,"外源调用异常");
        }
    }

    public ResponseResult executeSqls(Connection conn,String sql) {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            boolean result = statement.execute(sql);
            System.out.println(result);
        } catch (Exception e) {
            log.error("数据持久化失败", e);
            return ResponseResult.error(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return ResponseResult.ok();
    }

    public ResponseResult executeQuerySqls(Connection conn,String sql) {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData queryResultData = resultSet.getMetaData();
            int columnCount = queryResultData.getColumnCount();
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> dataMap = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    dataMap.put(queryResultData.getColumnName(i), resultSet.getObject(i));
                }
                dataMapList.add(dataMap);
            }
            return ResponseResult.ok().putData(dataMapList);
        } catch (Exception e) {
            log.error("数据持久化失败", e);
            return ResponseResult.error(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 多条记录插入操作
     * flag是为了判断是否是插入的最后一个记录
     */
    public static boolean affectRowMore(Connection conn,String sql, List<APIDATA> list) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = conn.prepareStatement(sql);
            for (APIDATA tUser : list) {
                preparedStatement.setLong(1, tUser.getID());
                preparedStatement.setString(2, tUser.getFILE_NAME());
                preparedStatement.setString(3, tUser.getTEMP_NAME());
                preparedStatement.setLong(4, tUser.getDS_ID());
                // 添加执行sql
                preparedStatement.addBatch();
            }
            // 执行操作
            int[] counts = preparedStatement.executeBatch(); // 执行Batch中的全部语句
            System.out.println(counts);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    /**
     * 获取查询总条数
     */
    public static int getTotal(Connection conn, String totalSql) throws SQLException {
        int total = 0;
        ResultSet totalResult = conn.prepareStatement("select count(1) from (" + totalSql + ") xx").executeQuery();
        if (totalResult.next()) {
            total = totalResult.getInt(1);
        }
        return total;
    }

    /**
     * 获取查询总条数
     */
    public static int getSum(Connection conn, String totalSql) throws SQLException {
        int total = 0;
        ResultSet totalResult = conn.prepareStatement(totalSql).executeQuery();
        if (totalResult.next()) {
            total = totalResult.getInt(1);
        }
        return total;
    }

    public static void responseWrite(Map result, HttpServletResponse response) {
        try (OutputStream out = response.getOutputStream()) {
            out.write(JSONObject.toJSONBytes(result, SerializerFeature.WriteMapNullValue));
            out.flush();
        } catch (IOException e) {
            log.error("文件流写失败", e);
        }
    }

    public static void responseWrite(byte[] bodyBytes, HttpServletResponse response) {
        try (ByteArrayInputStream in = new ByteArrayInputStream(bodyBytes); OutputStream out = response
                .getOutputStream()) {
            byte[] buf = new byte[1024];
            int len;
            while ((len = in.read(buf)) != -1) {
                out.write(buf, 0, len);
            }
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

//    private byte[] getBody(HttpResponse response) {
//        try (InputStream in = response.getEntity()
//                .getContent(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
//            byte[] bytes = new byte[1024];
//            int len;
//            while ((len = in.read(bytes)) != -1) {
//                out.write(bytes, 0, len);
//            }
//            return out.toByteArray();
//        } catch (IOException e) {
//            log.error("外源调用异常", e);
//            throw new RuntimeException("外源调用异常");
//        }
//    }

    public static Boolean executeSql(Connection conn,String sql) {
        Statement statement = null;
        Boolean result = false;
        try {
            statement = conn.createStatement();
            result = statement.execute(sql);
        } catch (Exception e) {
            log.error("数据持久化失败", e);
            return result;
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    /**
     * 从Connection构造表信息，用于获取表名
     */
    public List<Map<String, String>> assembleTableMeta(Connection conn) throws SQLException {
        List<Map<String, String>> tableMetaList = Lists.newArrayList();
        DatabaseMetaData nativeMeta = conn.getMetaData();
        ResultSet tables = nativeMeta.getTables(conn.getCatalog(), getSchemaPattern(nativeMeta), null, new String[]{"TABLE", "VIEW"});
        while (tables.next()) {
            Map<String, String> mp = Maps.newHashMap();
            mp.put("tableName", tables.getString("TABLE_NAME"));
            mp.put("tableType", tables.getString("TABLE_TYPE"));
            tableMetaList.add(mp);
        }
        return tableMetaList;
    }

    /**
     * 从Connection构造表信息
     */
    public List<Map<String, Object>> assembleColumnMeta(Connection conn, String tableName) throws SQLException {
        List<Map<String, Object>> columnMetaList = Lists.newArrayList();
        DatabaseMetaData nativeMeta = conn.getMetaData();
        ResultSet tables = nativeMeta.getTables(conn.getCatalog(), getSchemaPattern(nativeMeta), tableName, new String[]{"TABLE", "VIEW"});
        //表类型。 以下或特定于提供程序的值之一： "ALIAS"、"表"、"同义词"、"系统表"、"视图"、"全局临时"、"本地临时" 或 "系统视图"。
        while (tables.next()) {
            if (!tables.getString("TABLE_NAME").equalsIgnoreCase(tableName)) {
                continue;
            }
            ResultSet columns = nativeMeta.getColumns(null, getSchemaPattern(nativeMeta), tableName, null);
            while (columns.next()) {
                if (Arrays.asList("BLOB").contains(columns.getString("TYPE_NAME").toUpperCase())) {
                    continue;
                }
                Map<String, Object> mp = Maps.newHashMap();
                mp.put("columnName", columns.getString("COLUMN_NAME"));
                mp.put("columnType", columns.getString("TYPE_NAME"));
                mp.put("columnComment", columns.getString("REMARKS"));
                mp.put("dataType", columns.getInt("DATA_TYPE"));
                mp.put("paramType", paramTypeMapping(columns.getString("COLUMN_NAME")));
                String placeHolder = "请输入";
                if (JdbcTypeUtils.isDate(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入";
                }
                if (JdbcTypeUtils.isNumber(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入";
                }
                if (JdbcTypeUtils.isString(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入";
                }
                mp.put("placeHolder", placeHolder);

                columnMetaList.add(mp);
            }
        }
        return columnMetaList;
    }

    /**
     * columnType 映射出 paramType
     */
    public static String paramTypeMapping(String columnType) {
        if (null == columnType) {
            return "String";
        }
        if (columnType.equalsIgnoreCase("CHAR") || columnType.equalsIgnoreCase("VARCHAR") || columnType.equalsIgnoreCase("LONGVARCHAR")
                || columnType.toUpperCase().contains("CHAR")) {
            return "String";
        } else if (columnType.equalsIgnoreCase("INT") || columnType.equalsIgnoreCase("INTEGER") || columnType.equalsIgnoreCase("BIGINT")
                || columnType.equalsIgnoreCase("FLOAT") || columnType.equalsIgnoreCase("DOUBLE") || columnType.equalsIgnoreCase("BLOB")
                || columnType.equalsIgnoreCase("DECIMAL") || columnType.toUpperCase().contains("BLOB") || columnType.toUpperCase().contains("INT")) {
            return "Number";
        } else if (columnType.equalsIgnoreCase("TIMESTAMP") || columnType.equalsIgnoreCase("DATE") || columnType.equalsIgnoreCase("DATETIME")
                || columnType.toUpperCase().contains("DATE")) {
            return "DateTime";
        } else if (columnType.equalsIgnoreCase("BOOLEAN")) {
            return "Boolean";
        }
        return "String";
    }

    public static String executeQuerySql(Connection conn,String sql) {
        Statement statement = null;
        try {
            statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            ResultSetMetaData queryResultData = resultSet.getMetaData();
            int columnCount = queryResultData.getColumnCount();
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> dataMap = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    dataMap.put(queryResultData.getColumnName(i), resultSet.getObject(i));
                }
                dataMapList.add(dataMap);
            }
            return JSONObject.toJSONString(dataMapList);
        } catch (Exception e) {
            log.error("数据持久化失败", e);
            return (e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * SchemaPattern
     */
    private static String getSchemaPattern(DatabaseMetaData nativeMeta) {
        try {
            if (nativeMeta.getDatabaseProductName().toUpperCase().contains("ORACLE")) {
                return nativeMeta.getUserName();
            }
        } catch (SQLException throwables) {
            log.info("connection error");
        }
        return null;
    }

}